package dataint;

import java.sql.*;
import java.util.*;


/**
 * Class for connecting to the 1st DataSource aka the 1st MySQL Database
 */
public class DataSource1 extends DataSource {
    private String dbUri = "jdbc:mysql://penelope.uni-muenster.de:3306/dataint_source_1" +
            "?useUnicode=true&characterEncoding=UTF8";
    private String dbUser = "studentWS09";
    private String dbPasswd = "1994Inmon";

    private Connection connection = null;

    /**
     * Constructor
     */
    public DataSource1() {
        this.establishDBConnection();
    }

    /**
     * Establishes the connection to the database
     */
    private void establishDBConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            this.connection = DriverManager.getConnection(this.dbUri,
                    this.dbUser, this.dbPasswd);

        } catch (Exception e) {
            System.err.println("Something went wrong while connecting to the " +
                    "database.");
            System.exit(-1);
        }
    }

    /**
     * Fetches the data form the datasource
     */
    public void fetchData() {
        Statement  st = null;
        ResultSet  rs = null;


        String query = "SELECT " +
                "c.name, c.street, c.zip, c.city, c.email, c.premium," +
                " MAX(date) as date, SUM(l.amount * l.unitp *" +
		" CASE l.vat WHEN '19%' THEN 1.19 WHEN '7%' THEN 1.07 ELSE 1 END)" +
		" AS tov FROM customer AS c JOIN orderhead AS o ON c.id = o.cust " +
                "JOIN lineitems AS l ON o.id = l.oid GROUP BY o.cust";

        try {
            st = this.connection.createStatement();
            rs = st.executeQuery(query);

            while(rs.next()) {
                Customer currentCustomer = new Customer();

                currentCustomer.setFirstName(this.getFirstNameFromFullName(rs.getString("name")));
                currentCustomer.setLastName(this.getLastNameFromFullName(rs.getString("name")));
                currentCustomer.setStreetAddress(rs.getString("street"));
                currentCustomer.setCity(this.normalizeZipCode(rs.getString("zip")) + " " + rs.getString("city"));
                currentCustomer.setPhoneNo("");
                currentCustomer.setEmail(rs.getString("email"));
                currentCustomer.setPremium(this.convertPremium(rs.getString("premium")));
                currentCustomer.setDate(rs.getString("date"));
                currentCustomer.setTOV(this.normalizeTOV(rs.getDouble("tov")));

                // add Customer to List
                this.customerList.add(currentCustomer);
            }
        } catch (Exception e) {
            System.err.println("Something went wrong while fetching the data.");
            e.printStackTrace();
            System.exit(-1);
        } finally {
            try { if( rs != null) rs.close(); } catch( Exception e ) {}
            try { if( st != null) st.close(); } catch( Exception e ) {}
            try { if( this.connection != null) this.connection.close(); } catch( Exception e ) {}
        }
    }

    /**
     * Gets the First and Middle (if available) of the Fullname
     *
     * @param fullName
     * @return First and Middle Name
     */
    private String getFirstNameFromFullName(String fullName) {
        int i;
        String firstName = "";
        String[] tokens = fullName.split(" ");
        for (i = 0; i < (tokens.length - 1); i++) // omit the last string token
        {
            firstName = firstName + tokens[i] + " ";
        }
        return firstName.trim();
    }

    /**
     * Gets the Last name of the Full Name
     *
     * @param fullName
     * @return
     */
    private String getLastNameFromFullName(String fullName) {
        String[] tokens = fullName.split(" ");
        
        return tokens[tokens.length - 1];
    }

    /**
     * The DataSource determines the premium status of a customer by the string
     * N and P. However, for the internal data representation the premium status
     * is saved as boolean. Thus it needs to be converted.
     *
     * @param premium
     * @return
     */
    private String convertPremium(String premium) {
        if (premium.equals("N")) { return "No"; } else { return "Yes"; }
    }

    /**
     * As somehow the leading Zero of some Zipcodes was not saved by the
     * datasource they need to be added now.
     *
     * @param zip
     * @return
     */
    private String normalizeZipCode(String zip) {
        if (zip.length() < 5) { zip = "0" + zip; }

        return zip;
    }

    /**
     * As the datasource saved the values of the ordervalues in euro cents
     * they need to be converted to Euros
     * 
     * @param tov as euro cent
     * @return tov as euro
     */
    private double normalizeTOV(Double tov) {
        return tov / 100;
    }
}
